#!/usr/bin/env python
# -*- coding: utf-8 -*-

''' Author: Susana Messias n.º 6046 '''

import sqlite3
import csv
from operator import itemgetter

'''
Manages the database
'''
class Statistics:
    def __init__(self):
        self.lista = []
                
        conexao = sqlite3.connect("tabela.db")

        c = conexao.cursor()

        c.execute('''select * from tabela''')

        for line in c.fetchall():
            
            self.lista.append([line[0].encode('utf-8').replace('¨', '"'),
                         line[1].encode('utf-8').replace('♦', '"'),
                         line[2].encode('utf-8').replace('♦', '"'),
                         line[3].encode('utf-8').replace('♦', '"'),
                         line[4].encode('utf-8').replace('♦', '"'),
                         line[5].encode('utf-8').replace('♦', '"'),
                         line[6].encode('utf-8').replace('♦', '"'),
                         line[7].encode('utf-8').replace('♦', '"'),
                         line[8].encode('utf-8').replace('♦', '"')])            
            pass

        self.year = set()
        self.establishment = set()
        self.categorie = set()
        self.degree = set()

        for line in self.lista:
            self.year.add(line[6])
            self.establishment.add(line[8])
            self.categorie.add(line[4])
            self.degree.add(line[2])
            pass

        self.year = list(self.year)
        self.establishment = list(self.establishment)
        self.categorie = list(self.categorie)
        self.degree = list(self.degree)

        # Orders by alphabetic order
        self.year.sort()
        self.establishment.sort()
        self.categorie.sort()
        self.degree.sort()
        
        conexao.close()
        pass
    
    
    def countYearAndEstablishment(self, year, establishment):
        total = 0
        for line in self.lista:
            if (year == line[6] and establishment == line[8]):
                total = total + 1
                pass
            pass
        return total
    

    def creatCSV1(self, establishment):
        ficheiro = open('CSV3.csv', 'wb')
        spamWriter = csv.writer(ficheiro, delimiter=',')
        spamWriter.writerow(["Estabelecimento", "Ano Lectivo", "Nº Total de Professores"])
        for year in self.year:
            a = self.countYearAndEstablishment(year, establishment)
            spamWriter.writerow([establishment, year ,a])      
            pass
        ficheiro.close()
        pass
    
    
    def countYearEstablishmentAndCategorie(self, year, establishment, categorie):
        total = 0
        for line in self.lista:
            if (year == line[6] and establishment == line[8] and categorie == line[4]):
                total = total + 1
                pass
            pass        
        return total

    def creatCSV2(self, establishment, categorie ):
        ficheiro = open('CSV1.csv', 'wb')
        spamWriter = csv.writer(ficheiro, delimiter=',')
        spamWriter.writerow(["Categoria", "Estabelecimento", "Ano Lectivo", "Nº Total de Professores"])
        for year in self.year:
            b = self.countYearEstablishmentAndCategorie(year, establishment, categorie)
            spamWriter.writerow([categorie, establishment, year ,b])
            pass
        ficheiro.close()
        pass


    def countDegreeYearEstablishment(self, year, establishment, degree):
        total = 0
        for line in self.lista:
            if (degree == line[2] and year == line[6] and establishment == line[8]):
                total = total + 1
                pass
            pass        
        return total

    def creatCSV3(self, establishment, degree):
        ficheiro = open('CSV2.csv', 'wb')
        spamWriter = csv.writer(ficheiro, delimiter = ',')
        spamWriter.writerow(["Último Grau", "Estabelecimento", "Ano Lectivo", "Nº Total de Professores"])
        for year in self.year:
            c = self.countDegreeYearEstablishment(year, establishment, degree)
            spamWriter.writerow([degree, establishment, year, c])
            pass
        ficheiro.close()
        pass
  

    def firstHTML(self):
        ficheiro = file("index.html", "w")
        ficheiro.write("<html> <body>")
        ficheiro.write('<html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> </head> <body>')

        ficheiro.write('<h3>Nome do Estabelecimento - Ano - Nome do Professor</h3>')
        listaTemp = sorted(self.lista, key=itemgetter(8,6,1))
        
        for line in listaTemp:
            ficheiro.write('<li>' + line[8] + ' - ' + line[6] + ' - ' + line[1])
            pass
        
        ficheiro.write("</html> </body>") 
        ficheiro.close()
        pass
    

    def secondHTML(self):
        ficheiro = file("index.html", "w")
        ficheiro.write("<html> <body>")
        ficheiro.write('<html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> </head> <body>')
        ficheiro.write('<h3>Categoria - Nome do Estabelecimento - Ano - Nome do Professor</h3>')
        listaTemp = sorted(self.lista, key=itemgetter(4, 8, 6, 1))
        
        for line in listaTemp:
            ficheiro.write('<li>'+ line[4] + ' - ' + line[8] + ' - ' + line[6] + ' - ' + line[1])
            pass
        
        ficheiro.write("</html> </body>") 
        ficheiro.close()
        pass


    def thirdHTML(self):
        ficheiro = file("index.html", "w")
        ficheiro.write("<html> <body>")
        ficheiro.write('<html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> </head> <body>')
        listaTemp = sorted(self.lista, key=itemgetter(2, 8, 6, 1))
        ficheiro.write('<h3>Último Grau - Nome do Estabelecimento - Ano - Nome do Professor</h3>')
        for line in listaTemp:
            ficheiro.write('<li>'+ line[2] + ' - ' + line[8] + ' - ' + line[6] + ' - ' + line[1])
            pass
        
        ficheiro.write("</html> </body>") 
        ficheiro.close()
        pass

    
        
    
        
    
    
    

